# -*- coding: utf-8 -*-
# -*- coding: utf-8 -*-
import pymysql
import requests
import time
from datetime import datetime, timedelta
from flask import Flask, request, jsonify
from threading import Lock, Thread
import schedule
import logging
import re
from typing import Dict, List

# ================= 初始化配置 =================
app = Flask(__name__)

# MySQL数据库配置
MYSQL_CONFIG = {
    'host': '192.168.0.227',
    'user': 'root',
    'port': 3307,
    'password': 'abc123456.',
    'database': 'lottery_data_11_5',
    'charset': 'utf8mb4',
    'cursorclass': pymysql.cursors.DictCursor
}

API_KEY = "tpbimzqmwl"      # eleven1:tpbimzqmwl, eleven:fntxbtztir
BASE_URL = "https://api.duoqu.online"
REQUEST_LOCK = Lock()

# ================= 日志配置 =================
logging.basicConfig(
    level=logging.INFO,
    format='%(asctime)s - %(levelname)s - %(message)s',
    handlers=[
        logging.FileHandler('lottery_service.log'),
        logging.StreamHandler()
    ]
)
logger = logging.getLogger(__name__)


# ================= 数据库工具函数 =================
def sanitize_table_name(lottery_code: str) -> str:
    """将彩票代码转换为安全的表名"""
    # 替换特殊字符为下划线，并转换为小写
    table_name = re.sub(r'[^a-zA-Z0-9]', '_', lottery_code).lower()
    # 确保表名以字母开头
    if not table_name[0].isalpha():
        table_name = 't_' + table_name
    return table_name


def create_lottery_table(connection, table_name: str):
    """创建彩种专用表"""
    with connection.cursor() as cursor:
        cursor.execute(f'''
            CREATE TABLE IF NOT EXISTS `{table_name}` (
                id INT AUTO_INCREMENT PRIMARY KEY,
                bet_no VARCHAR(50) NOT NULL,
                gmt_bet_no VARCHAR(50),
                open_number VARCHAR(100) NOT NULL,
                block VARCHAR(100),
                hash VARCHAR(100),
                open_time DATETIME NOT NULL,
                fetch_time DATETIME NOT NULL,
                UNIQUE KEY uk_bet_no (bet_no)
            ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4
        ''')
    connection.commit()
    logger.info(f"表 {table_name} 初始化完成")


def init_db():
    """初始化数据库和所有彩种表"""
    try:
        connection = pymysql.connect(**MYSQL_CONFIG)

        # 创建基础表存储彩种元信息
        with connection.cursor() as cursor:
            cursor.execute('''
                CREATE TABLE IF NOT EXISTS lottery_meta (
                    id INT AUTO_INCREMENT PRIMARY KEY,
                    lottery_code VARCHAR(50) NOT NULL UNIQUE,
                    lottery_name VARCHAR(100) NOT NULL,
                    table_name VARCHAR(100) NOT NULL UNIQUE,
                    created_at DATETIME DEFAULT CURRENT_TIMESTAMP
                )
            ''')

        connection.commit()
        logger.info("数据库元表初始化完成")
    except Exception as e:
        logger.error(f"数据库初始化失败: {e}")
        raise
    finally:
        if connection:
            connection.close()


init_db()


# ================= 数据库连接助手 =================
def get_db_connection():
    """获取MySQL数据库连接"""
    return pymysql.connect(**MYSQL_CONFIG)


# ================= 数据采集器 =================
class DataCollector:
    def __init__(self):
        self.active_lottery_codes = [
            "Ethereum1-Cash5", "Ethereum3-Cash5", "Ethereum5-Cash5",
            "TRON1-Cash5", "TRON3-Cash5", "TRON5-Cash5",
            "Binance1-Cash5", "Binance3-Cash5", "Binance5-Cash5"
        ]
        self.last_request_time = {}

    def _ensure_table_exists(self, lottery_code: str, lottery_name: str) -> str:
        """确保彩种表存在并返回表名"""
        table_name = sanitize_table_name(lottery_code)
        connection = get_db_connection()
        try:
            with connection.cursor() as cursor:
                # 检查元表记录
                cursor.execute('''
                    SELECT table_name FROM lottery_meta 
                    WHERE lottery_code = %s
                ''', (lottery_code,))
                result = cursor.fetchone()

                if not result:
                    # 创建新表并记录元信息
                    create_lottery_table(connection, table_name)
                    cursor.execute('''
                        INSERT INTO lottery_meta 
                        (lottery_code, lottery_name, table_name)
                        VALUES (%s, %s, %s)
                    ''', (lottery_code, lottery_name, table_name))
                    connection.commit()
                else:
                    table_name = result['table_name']

            return table_name
        finally:
            connection.close()

    def _save_data_to_db(self, data: Dict) -> int:
        """保存数据到对应彩种表"""
        lottery_code = data['lotteryCode']
        lottery_name = data['lotteryName']
        table_name = self._ensure_table_exists(lottery_code, lottery_name)

        saved_count = 0
        fetch_time = datetime.now().strftime('%Y-%m-%d %H:%M:%S')
        connection = get_db_connection()

        try:
            with connection.cursor() as cursor:
                for item in data['data']:
                    try:
                        cursor.execute(f'''
                            INSERT INTO `{table_name}` (
                                bet_no, gmt_bet_no, open_number,
                                block, hash, open_time, fetch_time
                            ) VALUES (%s, %s, %s, %s, %s, %s, %s)
                            ON DUPLICATE KEY UPDATE 
                                open_number = VALUES(open_number),
                                fetch_time = VALUES(fetch_time)
                        ''', (
                            item.get('betNo', ''),
                            item.get('gmtBetNo', ''),
                            item.get('openNumber', ''),
                            item.get('block', ''),
                            item.get('hash', ''),
                            item.get('openTime', ''),
                            fetch_time
                        ))
                        if cursor.rowcount == 1:
                            saved_count += 1
                    except Exception as e:
                        logger.error(f"插入数据失败: {e}")

            connection.commit()
            return saved_count
        finally:
            connection.close()

    def fetch_and_save(self, lottery_code: str) -> bool:
        """获取并保存数据"""
        url = f"{BASE_URL}/data?code={lottery_code}&limit=30&key={API_KEY}&sort=desc"
        logger.info(f"请求API: {url.split('key=')[0]}...")

        try:
            response = requests.get(url, timeout=10)
            response.raise_for_status()
            data = response.json()

            if data.get('info') != 'success' or not data.get('data'):
                logger.error(f"API返回错误: {data.get('info')}")
                return False

            data['data'].sort(
                key=lambda x: datetime.strptime(x['openTime'], '%Y-%m-%d %H:%M:%S'),
                reverse=True
            )

            saved_count = self._save_data_to_db(data)
            logger.info(f"保存 {saved_count} 条数据到 {lottery_code} 表")
            return saved_count > 0

        except Exception as e:
            logger.error(f"采集失败: {e}")
            return False

    def run_collection_task(self):
        """定时采集任务"""
        logger.info("开始执行采集任务...")
        for code in self.active_lottery_codes:
            self.fetch_and_save(code)


# ================= API服务 =================
@app.route('/data', methods=['GET'])
def query_data():
    """查询接口（从对应彩种表获取数据）"""
    lottery_code = request.args.get('code', 'Ethereum1-Cash5')
    limit = min(int(request.args.get('limit', 5)), 100)
    date = request.args.get('date')

    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            # 获取表名
            cursor.execute('''
                SELECT table_name, lottery_name FROM lottery_meta
                WHERE lottery_code = %s
            ''', (lottery_code,))
            meta = cursor.fetchone()

            if not meta:
                return jsonify({
                    "info": "lottery code not found",
                    "lotteryCode": lottery_code,
                    "data": []
                }), 404

            # 构建查询
            query = f'''
                SELECT bet_no, gmt_bet_no, open_number,
                       block, hash, open_time
                FROM `{meta['table_name']}`
                WHERE 1=1
            '''
            params = []

            if date:
                query += ' AND DATE(open_time) = %s'
                params.append(date)

            query += ' ORDER BY open_time DESC LIMIT %s'
            params.append(limit)

            cursor.execute(query, params)
            results = cursor.fetchall()

            return jsonify({
                "info": "success",
                "lotteryCode": lottery_code,
                "lotteryName": meta['lottery_name'],
                "data": [{
                    "betNo": row['bet_no'],
                    "gmtBetNo": row['gmt_bet_no'],
                    "openNumber": row['open_number'],
                    "block": row['block'],
                    "hash": row['hash'],
                    "openTime": row['open_time'].strftime('%Y-%m-%d %H:%M:%S')
                    if isinstance(row['open_time'], datetime)
                    else row['open_time']
                } for row in results]
            })
    except Exception as e:
        logger.error(f"查询失败: {e}")
        return jsonify({"info": str(e)}), 500
    finally:
        connection.close()


# ================= 新增POST查询接口 =================
@app.route('/query', methods=['GET', 'POST'])
def query_by_betno():
    """
    查询大于指定期号的数据（支持GET/POST）
    GET请求参数：
        ?code=Ethereum1-Cash5&gmtBetNo=202506130761

    POST请求参数(JSON)：
        {"code":"Ethereum1-Cash5","gmtBetNo":"202506130761"}
    """
    # 获取参数（兼容GET/POST）
    if request.method == 'GET':
        lottery_code = request.args.get('code')
        gmt_bet_no = request.args.get('gmtBetNo')
    else:
        data = request.get_json() or {}
        lottery_code = data.get('code')
        gmt_bet_no = data.get('gmtBetNo')

    # 参数验证
    if not lottery_code or not gmt_bet_no:
        return jsonify({
            "info": "invalid request",
            "message": "必须提供code和gmtBetNo参数"
        }), 400

    if not gmt_bet_no.isdigit():
        return jsonify({
            "info": "invalid gmtBetNo",
            "message": "期号必须是数字"
        }), 400

    # 数据库查询
    connection = get_db_connection()
    try:
        with connection.cursor() as cursor:
            # 1. 获取表名
            cursor.execute('''
                SELECT table_name, lottery_name FROM lottery_meta
                WHERE lottery_code = %s
            ''', (lottery_code,))
            meta = cursor.fetchone()

            if not meta:
                return jsonify({
                    "info": "not found",
                    "message": f"未找到彩种 {lottery_code}"
                }), 404

            # 2. 查询数据（按数值比较期号）
            cursor.execute(f'''
                SELECT bet_no, gmt_bet_no, open_number,
                       block, hash, open_time
                FROM `{meta['table_name']}`
                WHERE CAST(gmt_bet_no AS UNSIGNED) > %s
                ORDER BY CAST(gmt_bet_no AS UNSIGNED) ASC
            ''', (int(gmt_bet_no),))

            results = cursor.fetchall()

            return jsonify({
                "info": "success",
                "lotteryCode": lottery_code,
                "lotteryName": meta['lottery_name'],
                "minBetNo": gmt_bet_no,
                "count": len(results),
                "data": [{
                    "betNo": row['bet_no'],
                    "gmtBetNo": row['gmt_bet_no'],
                    "openNumber": row['open_number'],
                    "block": row['block'],
                    "hash": row['hash'],
                    "openTime": row['open_time'].strftime('%Y-%m-%d %H:%M:%S')
                } for row in results]
            })

    except ValueError:
        return jsonify({
            "info": "error",
            "message": "期号必须是有效数字"
        }), 400
    except Exception as e:
        logger.error(f"查询失败: {str(e)}")
        return jsonify({
            "info": "error",
            "message": "服务器内部错误"
        }), 500
    finally:
        connection.close()
# ================= 定时任务 =================
collector = DataCollector()


def scheduled_job():
    try:
        collector.run_collection_task()
    except Exception as e:
        logger.error(f"定时任务异常: {e}")


schedule.every(1).seconds.do(scheduled_job)


def run_scheduler():
    while True:
        schedule.run_pending()
        time.sleep(1)


# ================= 主程序 =================
if __name__ == '__main__':
    # 初始化
    init_db()

    # 首次立即执行
    Thread(target=collector.run_collection_task).start()

    # 启动定时任务
    Thread(target=run_scheduler, daemon=True).start()

    # 启动API服务
    app.run(host='0.0.0.0', port=9999, threaded=True)



"""
11选5彩种：
以太坊11选5：Ethereum1-Cash5
以太坊3分11选5：Ethereum3-Cash5
以太坊5分11选5：Ethereum5-Cash5

波场11选5：TRON1-Cash5
波场3分11选5：TRON3-Cash5
波场5分11选5：TRON5-Cash5

币安11选5：Binance1-Cash5
币安3分11选5：Binance3-Cash5
币安5分11选5：Binance5-Cash5

接口说明：
请求方式 [GET]

[接口地址]?code=[彩票代码]&limit=[返回行数]&date=[日期]&key=[接口密钥]
最新查询示例：http://localhost:9999/data?code=Ethereum1-Cash5&limit=5

获取超过指定期号的数据：
http://localhost:9999/query?code=Ethereum1-Cash5&gmtBetNo=202506130772

参考地址：https://www.duoqu.online/lottery/Ethereum1-Cash5
"""